/**外包企业门户网站,生成时间:2017-04-21 11:52:13**/
package com.xbdun.dao.bmp.mapper;

import java.sql.Timestamp;
import java.util.List;
import java.util.Map;

import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.SelectProvider;
import org.apache.ibatis.annotations.Update;
import org.apache.ibatis.annotations.UpdateProvider;

import com.xbdun.dao.bmp.dto.AgentOrgDto;
import com.xbdun.dao.bmp.dto.LoginCanPagesDto;
import com.xbdun.dao.bmp.dto.LoginOrgUserMenuDto;
import com.xbdun.dao.bmp.dto.OrgUserDto;
import com.xbdun.dao.bmp.dto.QueryOrgUsersDto;
import com.xbdun.dao.bmp.dto.open.JoinOrgCodeByCodeDto;
import com.xbdun.dao.bmp.dto.open.OrgUserBkgeDto;
import com.xbdun.dao.bmp.entity.XbdunOrgUserDo;
import com.xbdun.dao.bmp.provider.OrgUserSqlProvider;


/**
 * 机构用户表
 * @author zhanggt
 * @2017-04-21 11:52:13
 */
@Mapper
public interface OrgUserMapper{
	
	/**
	* @Title: getByUserId
	* @Description	根据用户ID查询用户
	* @param userId	机构用户ID
	* @return XbdunOrgUserDo    返回类型
	*/
	@Select("SELECT * FROM xbdun_org_user WHERE userId=#{userId}")
	public XbdunOrgUserDo getByUserId(@Param("userId")String userId);
	/**
	 * 新增
	 * @param XbdunOrgUserDo 机构用户表
	 */
	@Insert("INSERT INTO xbdun_org_user(userId,userCode,orgCode,mobile,inauType,state,createTime,specilRole,invitType,realName,jonNum,orgEmail,enabled) VALUES(#{a.userId},#{a.userCode},#{a.orgCode},#{a.mobile},#{a.inauType},#{a.state},now(),#{a.specilRole},#{a.invitType},#{a.realName},#{a.jonNum},#{a.orgEmail},#{a.enabled})")
	public Long addXbdunOrgUser(@Param("a")XbdunOrgUserDo xbdunOrgUserDo);
	/**
	 * 动态更新参数,参数中Map可以必须跟Key为Where的更新条件<br/>
	 * <pre>
	 * 	例如：
	 *  Map<String,Object> setting = new HashMap<String,Object>();
	 *  setting.put("email", "360730440@qq.com");
	 *
	 *  Map<String,Object> where = new HashMap<String,Object>();
	 *  where.put("userId", "1222");
	 * </pre>
	 * Map的key值就是数据库字段<br/>
	 * Map的value就是值
	 */
	@UpdateProvider(type=OrgUserSqlProvider.class,method="updateDynXbdunOrgUserSql")
	public void updateDynXbdunOrgUser(@Param("param")Map<String,Object> param,@Param("_where")Map<String,Object> where);
	
	//------------------------//start分页查询--------------------------------------
	/**
	 * 分页查询 
	 * <pre>
	 * 	例如：
	 *  Map<String,Object> where = new HashMap<String,Object>();
	 *  param.put("orderby", "param.name,param age desc");
	 * </pre>
	 * @param _where where条件，现在支持And条件类型
	 * @param orderBy 排序条件
	 * @param pageNum 当前索引页
	 * @param numPerPage 每页显示条数
	 * @return
	 */
	@SelectProvider(type=OrgUserSqlProvider.class,method="queryPagerSql")
	public List<OrgUserDto> queryPager(@Param("_where")Map<String,Object> where,@Param("_orderby")String orderBy,@Param("pageIndex")int pageIndex,@Param("pageSize")int pageSize);
	
	//根据用户编号查询所属机构总量
	@Select("SELECT COUNT(*) FROM xbdun_sys_org a,xbdun_org_user b WHERE a.orgCode = b.orgCode and b.userCode = ${userCode}")
	public int queryOrgUsersPagerCnt(@Param("userCode")long userCode);
	
	//根据用户编号查询所属机构
	@Select("SELECT a.orgName,b.userId,b.realName,b.orgCode,b.orgEmail,b.mobile,b.jonNum,b.createTime,b.specilRole FROM xbdun_sys_org a,xbdun_org_user b WHERE a.orgCode = b.orgCode and b.userCode = ${userCode} ORDER BY b.createTime DESC LIMIT ${pageIndex},${pageSize}")
	public List<QueryOrgUsersDto> queryOrgUsersPager(@Param("userCode")long userCode,@Param("pageIndex")int pageIndex,@Param("pageSize")int pageSize);
	
	/**
	 * 分页查询返回总数量
	 * <pre>
	 * 	例如：
	 *  Map<String,Object> where = new HashMap<String,Object>();
	 *  param.put("orderby", "param.name,param age desc");
	 * </pre>
	 * @param where 参数为where约束参数
	 * @return
	 */
	@SelectProvider(type=OrgUserSqlProvider.class,method="queryPagerCountSql")
	public int queryPagerCount(@Param("_where")Map<String,Object> where);
	//-------------------------//end--------------------------------------------

	/**
	* @Title: existJobNum
	* @Description: 查询当前机构下是否已经存在了员工编号
	* @param jonNum	员工编号
	* @param orgCode	当前机构编号
	* @return int    返回类型
	 */
	@SelectProvider(type=OrgUserSqlProvider.class,method="existJobNumSql")
	public int existJobNum(@Param("jonNum")String jonNum,@Param("orgEmail")String orgEmail,@Param("orgCode")long orgCode);
	
	/**
	 * 部门角色权限+特殊权限type=2 UNION 特殊权限 type=1
	* @Title: queryOrgUserOffePower
	* @Description	查询机构用户下所有有效权限，排除
	* @param userCode
	* @param orgCode
	* @return List<String>    返回类型
	*/
	@Select("SELECT CAST(b.code AS CHAR) FROM xbdun_power_user_menu a ,xbdun_power_role_depart b WHERE a.orgCode = b.orgCode AND a.userCode = #{userCode} AND a.orgCode=#{orgCode} AND a.orleDepartId = b.orleDepartId AND b.code NOT IN (SELECT d.code FROM xbdun_power_user_spec d WHERE d.orgCode = #{orgCode} AND d.userCode = #{userCode} AND d.type=2) UNION SELECT CAST(c.code AS CHAR) FROM xbdun_power_user_spec c  WHERE c.orgCode = #{orgCode} AND c.userCode = #{userCode} AND c.type=1")
	public List<String> queryOrgUserPower(@Param("userCode")long userCode,@Param("orgCode")long orgCode);
	
	/**
	* 查询当前机构下当前用户的拥有的有效菜单项
	* @Title: loginOrgUserMenu
	* @Description 查询当前机构下当前用户的拥有的有效菜单项
	* @param platCode	平台Code
	* @param userCode   平台用户编号
	* @param orgCode	平台用户机构编号
	* @return List<LoginOrgUserMenuDto>    当前登录的用户有效菜单
	 */
	@Select("SELECT ax.code,ax.name,ax.hit title,ax.target,ax.icoUrl,ax.url,ax.menuType FROM xbdun_sys_menu ax,(SELECT b.code FROM xbdun_power_user_menu a ,xbdun_power_role_depart b WHERE a.orgCode = b.orgCode AND a.userCode = #{userCode} AND a.orgCode=#{orgCode} AND b.code LIKE CONCAT(#{platCode},'%') AND a.orleDepartId = b.orleDepartId AND b.code NOT IN (SELECT d.code FROM xbdun_power_user_spec d WHERE d.orgCode = #{orgCode} AND d.userCode = #{userCode} AND d.type=2 AND d.`code` LIKE CONCAT(#{platCode},'%')) UNION SELECT c.code FROM xbdun_power_user_spec c  WHERE c.orgCode = #{orgCode} AND c.userCode = #{userCode} AND c.type=1 AND c.`code` LIKE CONCAT(#{platCode},'%'))bx WHERE ax.code = bx.code AND ax.menuType <>3 AND ax.blDel = 2 AND ax.enable = 1 AND ax.code LIKE CONCAT(#{platCode}, '%') AND ax.blDisplay = 1 ORDER BY ax.sort ASC")
	public List<LoginOrgUserMenuDto> loginOrgUserMenu(@Param("platCode")int platCode,@Param("userCode")long userCode,@Param("orgCode")long orgCode);
	
	
	/**
	* 查询当前用户的所有的部门+角色信息
	* @Title: getOrgUserDepartRole
	* @Description: 查询当前用户的所有的部门+角色信息     例: 市场部/管理员+超级管理员,人事部/员工
	* @param userCode	用户ID
	* @return String    返回类型
	 */
	@Select("SELECT CONCAT(a.departName,'/',b.roleName) FROM xbdun_depart a,xbdun_role b,(SELECT code,roleId FROM xbdun_orle_depart	WHERE id IN (SELECT orleDepartId FROM xbdun_power_user_menu WHERE userCode = #{userCode} AND orgCode = #{orgCode}))c WHERE a.code = c.code AND b.id = c.roleId AND a.orgCode = #{orgCode}")
	public List<String> getOrgUserDepartRole(@Param("userCode")long userCode, @Param("orgCode")long orgCode);
	
	/**
	* 删除机构用户
	* @Title: deleteOrgUser
	* @Description: 删除机构用户
	* @param userId	用户ID
	* @return int    返回类型
	 */
	@Delete("DELETE FROM xbdun_org_user WHERE userId = #{userId} AND version = #{version}")
	public int deleteOrgUser(@Param("userId")String userId, @Param("version")Timestamp version);

	/**
	* 修改当前用户的机构用户信息
	* @Title: modifyOrgUserById
	* @Description: 修改当前用户的机构用户信息
	* @param param	用户ID
	* @return int    返回类型
	 */
	@UpdateProvider(type=OrgUserSqlProvider.class,method="modifyOrgUserByIdSql")
	public int modifyOrgUserById(@Param("param")Map<String, Object> param, @Param("where")Map<String, Object> where);
	
	/**
	* @Title: loginCanPages
	* @Description: 机构用户下登录不同平台 这个性能有点差
	* @param orgCode
	* @param userCode
	* @return    设定文件
	* @return List<LoginCanPagesDto>    返回类型
	*/
	//@Select("SELECT ax.platCode,ax.url,ax.platName name,ax.platDescrip,ax.platCode secretKey,ax.secretVal FROM xbdun_platform_info ax,(SELECT b.code FROM xbdun_power_user_menu a ,xbdun_power_role_depart b WHERE a.orgCode = b.orgCode AND a.userCode = #{userCode} AND a.orgCode=#{orgCode} AND a.orleDepartId = b.orleDepartId AND b.code NOT IN (	SELECT d.code FROM xbdun_power_user_spec d WHERE d.orgCode = #{orgCode} AND d.userCode = #{userCode} AND d.type=2 AND d.code<=999) UNION SELECT CAST(c.code AS CHAR) FROM xbdun_power_user_spec c  WHERE c.orgCode = #{orgCode} AND c.userCode = #{userCode} AND c.type=1 AND c.code<=999) bx WHERE ax.platCode = bx.code AND ax.enabled = 1 AND ax.blManager IN (2,#{blManager}) GROUP BY ax.platCode")
//	@Select("SELECT ax.platCode,ax.url,ax.platName name,ax.platDescrip,ax.platCode secretKey,ax.secretVal FROM xbdun_platform_info ax WHERE ax.platCode in (select hashPlats from xbdun_org_user c where c.userCode = #{userCode} and c.orgCode = #{orgCode}) AND ax.enabled = 1 GROUP BY ax.platCode")
	@Select("SELECT ax.platCode,ax.url,ax.platName name,ax.platDescrip,ax.platCode secretKey,ax.secretVal FROM xbdun_platform_info ax WHERE FIND_IN_SET(ax.platCode,(SELECT hashPlats FROM xbdun_org_user c WHERE c.userCode =${userCode} AND c.orgCode = ${orgCode})) AND ax.enabled = 1 GROUP BY ax.platCode ")
	public List<LoginCanPagesDto> loginCanPages(@Param("orgCode")Long orgCode,@Param("userCode")Long userCode);
	//平台管理返回所有有效的平台一个用信息
	@Select("SELECT platCode,url,platName name,platDescrip,platCode secretKey,secretVal FROM xbdun_platform_info WHERE enabled = 1")
	public List<LoginCanPagesDto> xbdunCanPages();
	@Select("SELECT a.platCode,a.url,a.platName name,a.platDescrip,a.platCode secretKey,a.secretVal FROM xbdun_platform_info a,(SELECT code FROM xbdun_sys_org_menus WHERE orgCode = ${orgCode} AND code<1000)b WHERE a.platCode = b.code AND a.enabled = 1 ORDER BY platCode")
	public List<LoginCanPagesDto> supperCanPages(@Param("orgCode")long orgCode);
	
	//根据机构编号+用户编号获取机构用户基本信息
	@Select("SELECT a.orgName,b.userId,b.realName,b.orgCode,b.orgEmail,b.mobile,b.jonNum,b.createTime,b.specilRole FROM xbdun_sys_org a,xbdun_org_user b WHERE a.orgCode = b.orgCode AND b.orgCode = #{orgCode} AND  b.userCode = #{userCode}")
	public QueryOrgUsersDto getUserByOrgUserCode(@Param("orgCode")Long orgCode,@Param("userCode")Long userCode);
	//根据当前机构+用户编号查询到当前用户的角色
	@Select("SELECT specilRole FROM xbdun_org_user WHERE userCode = #{userCode} AND orgCode = #{orgCode} AND enabled = 1")
	public Integer getOrgUserRoleId(@Param("orgCode")Long orgCode,@Param("userCode")Long userCode);
	//如果是平台管理员那么就获取系统默认的菜单项
	@Select("SELECT code,name,hit title,target,icoUrl,url,menuType FROM xbdun_sys_menu WHERE menuType <> 3 AND blPlat = 1 AND blDel = 2 ORDER BY SORT")
	public List<LoginOrgUserMenuDto> loginXbdunDefMenu();
	@Select("SELECT code,name,hit title,target,icoUrl,url,menuType FROM xbdun_sys_menu WHERE menuType <> 3 AND code LIKE '${platCode}%' AND blDel = 2 ORDER BY SORT")
	public List<LoginOrgUserMenuDto> loginXbdunPlatCodeMenu(@Param("platCode")int platCode);
	//获取平台机构
	@Select("SELECT orgCode  FROM xbdun_org_user WHERE specilRole=5 AND enabled=1")
	public Long getXbdunOrgCode();
	//获取机构用户时间戳
	@Select("SELECT version FROM xbdun_org_user WHERE orgCode=#{orgCode} AND userCode=#{userCode}")
	public Timestamp getOrgUserTime(@Param("orgCode")long orgCode,@Param("userCode")long userCode);
	//获取从前的机构对应的平台用户
	@Select("SELECT version,userId,orgCode,oldSpecilRole FROM xbdun_org_user WHERE specilRole=5 AND enabled=1")
	public AgentOrgDto getOldXbdOrgUser();
	//将从前的平台用户修改为普通用户 超级管理员
	@Update("UPDATE xbdun_org_user SET oldSpecilRole=5,specilRole=3 WHERE version=#{version} AND userId=#{userId}")
	public int setOldAgentOrgUser(@Param("userId")String userId,@Param("version")Timestamp timestamp);
	//获取机构最大的权限的角色
	@Select("SELECT version,userId,specilRole FROM xbdun_org_user WHERE orgCode = ${orgCode} and  specilRole in (2,3,4) ORDER BY specilRole DESC LIMIT 1")
	public Map<String, Object> selectSuperAdmin(@Param("orgCode")long orgCode);
	//修改机构用户为特殊角色
	@Update("UPDATE xbdun_org_user SET oldSpecilRole=#{specilRole},specilRole=5 WHERE version=#{version} AND userId=#{userId}")
	public int setAgentOrgUser(@Param("version")Timestamp timestamp,@Param("userId")String userId,@Param("specilRole")int specilRole);
	//还原老角色
	@Update("UPDATE xbdun_org_user SET oldSpecilRole=1,specilRole=#{specilRole} WHERE version=#{version} AND userId=#{userId}")
	public int restoreRole(@Param("userId")String string,@Param("specilRole")int specilRole,@Param("version")Timestamp timestamp);
	//获取老的角色
	@Select("SELECT version,userId,orgCode FROM xbdun_org_user WHERE oldSpecilRole = 5")
	public AgentOrgDto getOldXbdunRole();
	
	//对应机构下用户权限
	@Select("SELECT GROUP_CONCAT(CAST(ax.code AS CHAR)) FROM xbdun_sys_menu ax,(SELECT b.code FROM xbdun_power_user_menu a ,xbdun_power_role_depart b WHERE a.orgCode = b.orgCode AND a.userCode = #{userCode} AND a.orgCode=#{orgCode} AND b.code LIKE CONCAT(#{platCode},'%') AND a.orleDepartId = b.orleDepartId AND b.code NOT IN (SELECT d.code FROM xbdun_power_user_spec d WHERE d.orgCode = #{orgCode} AND d.userCode = #{userCode} AND d.type=2 AND d.`code` LIKE CONCAT(#{platCode},'%')) UNION SELECT c.code FROM xbdun_power_user_spec c  WHERE c.orgCode = #{orgCode} AND c.userCode = #{userCode} AND c.type=1 AND c.`code` LIKE CONCAT(#{platCode},'%'))bx WHERE ax.code = bx.code AND ax.menuType <>1 AND ax.blDel = 2 AND ax.enable = 1 AND ax.code LIKE CONCAT(#{platCode}, '%') ORDER BY ax.sort")
	public String orgUserAuth(@Param("platCode")int platCode,@Param("userCode")long userCode,@Param("orgCode")long orgCode);
	//平台管理员获取当前平台的所有权限
	@Select("SELECT GROUP_CONCAT(CAST(ax.code AS CHAR)) FROM xbdun_sys_menu ax WHERE ax.menuType <>1 AND ax.blDel = 2 AND ax.enable = 1 AND ax.code LIKE CONCAT(#{platCode}, '%') ORDER BY ax.sort")
	public String orgUserAuthAdmin(@Param("platCode")int platCode);
	
	//查询当前机构下超级管理员以及admins拥有的有效菜单项
	@Select("SELECT a.code,a.name,a.hit title,a.target,a.icoUrl,a.url,a.menuType FROM xbdun_sys_menu a WHERE	a.menuType <> 3 AND	a.blDel = 2 AND	a.code IN (SELECT code FROM xbdun_sys_org_menus WHERE orgCode = #{orgCode} AND code LIKE CONCAT(#{platCode},'%')) ORDER BY SORT")
	public List<LoginOrgUserMenuDto> loginOrgSupAdMenu(@Param("platCode")int platCode, @Param("orgCode")long orgCode);
	
	//查找当前机构的企业法人信息
	@Select("SELECT b.* FROM xbdun_sys_org a,xbdun_org_user b WHERE a.userCode = b.userCode AND a.orgCode=b.orgCode AND b.orgCode = ${orgCode}")
	public XbdunOrgUserDo getOrgBusiPerson(@Param("orgCode")long orgCode);
	
	//根据用户编号查询已经加入的机构
	@Select("SELECT orgCode,blDel FROM xbdun_org_user WHERE userCode =#{userCode} AND enabled = 1 ORDER BY createTime DESC")
	public List<JoinOrgCodeByCodeDto> joinOrgCodeByCode(String userCode);
	//根据机构,用户编号获取用户角色
	@Select("SELECT inauType,specilRole FROM xbdun_org_user WHERE userCode =#{userCode} AND orgCode=#{orgCode}")
	public OrgUserBkgeDto getOrgUserBkge(@Param("orgCode")long orgCode,@Param("userCode")long userCode);
	
	//判断用户是否存在于该机构
	@Select("SELECT COUNT(*) FROM xbdun_org_user WHERE userCode = ${userCode} AND orgCode = ${orgCode}")
	public int existOrgUser(@Param("userCode")Long userCode, @Param("orgCode")long orgCode);

	//查看机构用户姓名以及是否在职
	@Select("SELECT realName,blDel FROM xbdun_org_user WHERE orgCode = ${orgCode} AND userCode = ${userCode}")
	public QueryOrgUsersDto getUserIsWork(@Param("orgCode")Long orgCode, @Param("userCode")Long userCode);
	
	//修改机构用户可登陆平台
	@Update("UPDATE xbdun_org_user SET hashPlats = #{plats} WHERE userCode = ${userCode} AND orgCode = ${orgCode}")
	public void updateOrgUserHasPlats(@Param("userCode")long userCode, @Param("orgCode")long orgCode, @Param("plats")String plats);
}